
%Global header1 header2 header3 header4 header5 header6 header7 header8 header9 header0 NBR_REPORT_COLUMN;


%macro severity (wherefl, rel, reltxt, popfl=%str(saffl="Y"));

*******************************************************************************************************
Select data for the current pool
******************************************************************************************************;

data temporary(where=(rel=&rel));
  length trtp $100.;
  set adam.adae; 
  if &popfl and TRTEMFL='Y' and trtan in(4 5 6);
  tocount=1;
if areln = 1 then rel = 1; 	
else if areln = 2 then rel = 2;	
else if areln = 3 then rel = 3;	
else if areln = 4 then rel = 4;
  if trta='A' then do;
		trtpn=1;
		trtp='Study Product A';
		column=1;
	end;
	if trta='B' then do;
		trtpn=2;
		trtp='Study Product B';
		column=2;
	end;
	if trta='C' then do;
		trtpn=3;
		trtp='Study Product C';
		column=3;
	end;
run;

 data pop; 
  set adam.adsl(where=(&popfl)); 
;
  by usubjid;
  length trtp $40.;
/*  sexc=upcase(sexc);*/
  tocount=1; 
  part=scan(actarmcd, 2, '/');
   trt1=substr(part, 1, 1);
   trt2=substr(part, 2, 1);
   trt3=substr(part, 3, 1);
   if trt1='A' or TRT2='A' or TRT3='A'  then do; trtp='Study Product A'; TRTPN=1; column=1;
output;end;
  if trt1='B' or TRT2='B' or TRT3='B' then do; trtp='Study Product B';  TRTPN=2; column=2;
output;  end;
if trt1='C' or TRT2='C' or TRT3='C'  then do; trtp='Study Product C';  TRTPN=3; column=3;
output; end;
run;

proc sort data=pop nodupkey out=pop dupout=check_; 
by usubjid trtp; 
run; 

data pop;
set pop;
where trtp ne '';
run;

proc sql noprint;
 select strip(put(sum(case when(trtpn EQ 1) then 1 else 0 end),8.)),
        strip(put(sum(case when(trtpn EQ 2) then 1 else 0 end),8.)),
        strip(put(sum(case when(trtpn EQ 3) then 1 else 0 end),8.))
 into :pop1,
      :pop2,
      :pop3
 from pop;
quit;

Proc sort data =pop out = spopone nodupkey ;
   by column trtp;
run;
Proc Print data = spopone noobs n width = min;
var column trtp;
run;
data tofmt;
  set spopone;
run;

%makefmt(tofmt, column , trtp, _ftrt);

*******************************************************************************************************
Define headers for each columns
******************************************************************************************************;
data _heads;
  format n1-n3 $15. lbl1-lbl3 $150.;

  n1 = "(N=%cmpres(&pop1))";
  n2 = "(N=%cmpres(&pop2))";
  n3 = "(N=%cmpres(&pop3))";
  *n4 = "(N=%cmpres(&pop4))";
  *n5 = "(N=%cmpres(&pop5))";

  lbl1 = strip(put(1, _ftrt.));
  lbl2 = strip(put(2, _ftrt.));
  lbl3 = strip(put(3, _ftrt.));
  *lbl4 = strip(put(4, _ftrt.));
  *lbl5 = strip(put(5, _ftrt.));

  call symput("N1",n1);
  call symput("N2",n2);
  call symput("N3",n3);
  *call symput("N4",n4);
  *call symput("N5",n5);

  call symput("lbl1",lbl1);
  call symput("lbl2",lbl2);
  call symput("lbl3",lbl3);
  *call symput("lbl4",lbl4);
  *call symput("lbl5",lbl5);
run;

proc sql noprint;
create table headers
(column num,
  header char(200));
insert into headers
   %do i = 1 %to 3;
      values(&i, "&&lbl&i.~%str(&&N&i)")
   %end;
;

select header
into :header1-:header99
from headers
where column LE 3
order by column;
quit;

*******************************************************************************************************
Create columns macros
******************************************************************************************************;
data _null_;
   call symput("nbr_report_column", "3");
   call symput("column1", "COL1");
   call symput("column2", "COL2");
   call symput("column3", "COL3");
   call symput("columnz&rel.", "COL1&rel. COL2&rel. COL3&rel.");
run;

*******************************************************************************************************
Data manipulation
******************************************************************************************************;
proc sql;
 create table all as

 /* Any AE... */
 /*
 select 1 as level1,
        " " as level1_label length=200, 
        1 as level2,
        "Any SPUEAE" as level2_label length=200,
        column,
        count(distinct(usubjid)) as count
 from temporary
 where TRTEMFL="Y" and AOCC07FL="Y"
 group by 5*/

 select 1 as level1,
        " " as level1_label length=200, 
        1 as level2,
        "Any SPUEAE" as level2_label length=200,
        column,
        count(aedecod) as count
 from temporary
 where TRTEMFL="Y" and rel ne .
 group by 5

 outer union corr

 /* SOC Level */
 select 2 as level1,
        aebodsys as level1_label length=200,
        1 as level2,
        aebodsys as level2_label length=200,
        column,
        count(distinct(usubjid)) as count
 from temporary
 where TRTEMFL="Y" and rel ne . /*AOCC08FL="Y"*/
 group by 2,4,5

 outer union corr

 /* SOC and Preferred Term Level */
 select 2 as level1,
        aebodsys as level1_label length=200,
        2 as level2,
        "\li150"||propcase(trim(aedecod)) as level2_label length=200,
        column,
        count(distinct(usubjid)) as count
 from temporary
 where TRTEMFL="Y" and rel ne . /*AOCC09FL="Y"*/
 group by 2,4,5

 order by 1,2,3,4,5;
 select count(distinct column) into:nobs from all;
quit;

proc transpose data=all out=report prefix=column;
 by level1 level1_label level2 level2_label;
 var count;
 id column;
run;
%if &nobs=0 %then %do;
data report;
set report;
level1=1;
level1_label=" ";
level2=1;
level2_label="Any SPUEAE";
column1=0;
column2=0;
run;
%end;

*******************************************************************************************************
Create report dataset
******************************************************************************************************;
data report1;
 set report;
 by level1 level1_label level2 level2_label /*level3 level3_label*/;
 format sort1&rel sort2&rel 8.;
 retain sort1&rel;
 if first.level1_label then do;
 %do j = 1 %to &nbr_report_column; if missing(column&j) then column&j=.; %end;
 sort1&rel=sum(0 %do i = 1 %to &nbr_report_column; , coalesce(column&i,0) %end;);
 sort2&rel=sum(0 %do i = 1 %to &nbr_report_column; , coalesce(column&i,0) %end;);
 end;
run;

proc sort data=report1 out=report1B;
 by level1 descending sort1&rel level1_label level2 descending sort2&rel level2_label;
run;



%do i = 1 %to &nbr_report_column;

data report1b; 
set report1b; 
if column&i = . then column&i=0; 
run; 

proc sql noprint; 
select column&i into :denomi&i
from report1B
where level1 eq 1 and column&i > .z ; 
quit; 

%put denom = &&denomi&i.; 

%end;
 
data report2&rel;
 length level1_label level2_label $200 &&columnz&rel. $25;
 retain level1 level1_label level2 level2_label &&columnz&rel.;
 set report1B;
 by level1 descending sort1&rel level1_label level2 descending sort2&rel level2_label;
 if level1;

 %do i = 1 %to &nbr_report_column;
    
       if column&i in (.,0) then do;
              column&i = 0;
              col&i&rel = put(column&i, 3.);
       end;
       
else col&i&rel = put(column&i, 3.)||" ("||put(column&i/ &&pop&i. * 100, 5.1)||")";
        *else col&i&rel = put(column&i, 3.)||" ("||put(column&i/ &&denomi&i. * 100, 5.1)||")";
 %end;

 level2_label = trim(level2_label) || " [n(%)]";
 keep level1 level1_label level2 level2_label &&columnz&rel sort1&rel sort2&rel;
run;

proc sort data = report2&rel;
  by level1 level1_label level2 level2_label;
run;

%mend Severity;

%macro prnt (program, disname,idisname, ds_list, msg, where, rel, pop=saf);
%Severity(&where, rel=1, reltxt=Unrelated);
%Severity(&where, rel=2, reltxt=Unlikely Related);
%Severity(&where, rel=3, reltxt=Possibly Related);
%Severity(&where, rel=4, reltxt=Probably Related);
/*
%Severity(&where, rel=1, reltxt=Possibly Related);
%Severity(&where, rel=2, reltxt=Unrelated);
%Severity(&where, rel=3, reltxt=Unlikely Related);
%Severity(&where, rel=4, reltxt=Probably Related);
%Severity(&where, rel=5, reltxt=Definitely);
*/
data report2;
 merge report21 report22 report23 report24 /*report25*/ ;
 by level1 level1_label level2 level2_label;
 format sort1 sort2 8.;
 retain sort1;
 if first.level1_label then 
 sort1=coalesce(sort11,0)+coalesce(sort12,0)+coalesce(sort13,0)+coalesce(sort14,0)/*+coalesce(sort15,0)*/;
 sort2=coalesce(sort21,0)+coalesce(sort22,0)+coalesce(sort23,0)+coalesce(sort24,0)/*+coalesce(sort25,0)*/;
run;

proc sort data = report2;
 by level1 descending sort1 level1_label level2 descending sort2 level2_label;
run;

data report3;
 set report2;
 by level1 descending sort1 level1_label level2 descending sort2 level2_label;
 retain nb page 1;
 if level2 EQ 1 and nb GT 12 then do;
  nb=1;
  page+1;
 end;
 else nb+1;
 %do j = 1 %to &nbr_report_column;
  %do k = 1 %to 5;    
    if col&j&k EQ " " then col&j&k = "  0";
    if level1 = 1 then col&j&k = trim(scan(col&j&k,1,'(')); 
    if level1 = 1 then level2_label = 'Any SPUEAE [n]';  
  %end;
 %end;
 space = " ";
run;

data _null_;
  call symput("subheader1", "Unrelated");
  call symput("subheader2", "Unlikely");
  call symput("subheader3", "Possibly");
  call symput("subheader4", "Probably");
  *call symput("subheader5", "Definitely");
  
run;

*******************************************************************************************************
Printing using proc report
******************************************************************************************************;
title3 "Table &disname (Continued)";

filename filetmp temp;
filename filertf "\\algopharm.com\algorithmepharmadata\Biostudies\Montreal\&prot.\SRA\Biostatistics\Work\Outputs\tables\T14.3.1.5.2.AE.REL.rtf";

/*
option orientation=landscape missing=" ";
ods listing close;
ods escapechar="^";
ods rtf file=filetmp style=styles.Algo2;
*/
%open_rtf;

%empty_ds_fix(report3);
%put nobs=&nb_obs;
title4 "Summary of Study Product Use-Emergent Adverse Events by System Organ Class, MedDRA Preferred Term and Relationship - Part B";
title5 "(Safety Population)";

%footer1(foot1=1, ds_list=&ds_list., Program=&program.);
/*footnote3 j=l "Note(s): Treatment A = 16.1 mg KP511 API (Test); Treatment B = 8 mg HM API (Reference).";*/
*footnote3 j=l "Note(s): Adverse Events are coded using MedDRA 19.1.";
footnote3 j=l "Note(s): For the subject counts, each Study Product Use-Emergent adverse event is counted only once for each subject within
 each System Organ Class and";
footnote4 j=l "MedDRA Preferred Term.";


   proc report data=report3 nowd split='~' missing;
    column page level1 sort1 level1_label level2 sort2 ("&line System Organ Class ~MedDRA Preferred Term" level2_label) 
           ("\line  &header1. " col11 col12 col13 col14 )  /*space*/
           ("\line  &header2. " col21 col22 col23 col24 ) /*space*/
           ("\line  &header3. " col31 col32 col33 col34 );
    define page         / " " order order=internal noprint;
    define level1       / " " order order=internal noprint;
    define sort1        / " " order descending order=internal noprint;
    define level1_label / " " order order=internal noprint;
    define level2       / " " order order=internal noprint;
    define sort2        / " " order descending order=internal noprint;
    define level2_label / " " order=internal style(column)=[cellwidth=1.2in just=left] style(header)=[just=left];
    *define space        / "&linebot. " order=internal style(column)=[cellwidth=0.03in just=left] style(header)=[just=left];

    %do j = 1 %to 3;
     %do k = 1 %to 4; 
      define col&j&k / "&&subheader&k." style(column)=[cellwidth=0.72in just=left] style(header)=[just=center];
     %end;
    %end;

    break after page   / page;

    %if &nb_obs GT 0 %then %do;
    compute before level1_label;
     line " ";
    endcomp;
    %end;

    %if &nb_obs EQ 0 %then %do;
     compute after page/style=[cellwidth=7.5in just=left];
      line "No &msg were reported.";
     endcomp;
    %end;
   run;

ods rtf close;
ods listing;
%arrange_rtf;

%mend prnt;



%prnt(program=T14_03_01_05_02_AE_REL, disname=14.3.1.5.2, idisname=14.3.1.5.2, ds_list=ADAE, msg=Adverse Events, where=%bquote());



proc datasets lib=work kill nolist nodetails;
run;
